﻿using QuanLyThuVien.DTO;
using QuanLyThuVien.Supporter;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QuanLyThuVien.DAL
{
    //----------------------------------------------------------------------
    //Desc: class kết nối với bảng SACH
    //----------------------------------------------------------------------
    public class DALSach
    {
        //----------------------------------------------------------------------
        //Desc: constructor mặc định
        //----------------------------------------------------------------------
        public DALSach()
        { }

        //----------------------------------------------------------------------
        //Desc: chuyển dòng dữ liệu thành SACH
        //----------------------------------------------------------------------
        public DTOSach ConvertToSach(DataRow row)
        {
            DTOSach sach = new DTOSach();
            sach.MaSach = row["MaSach"].ToString();
            sach.MaDauSach = row["MaDauSach"].ToString();
            try
            {
                sach.SoLuong = (int)row["SoLuong"];
            }
            catch { }
            try
            {
                sach.NamXB = (int)row["NamXB"];
            }
            catch { }
            sach.NhaXB = row["NhaXB"].ToString();
            try
            {
                sach.TriGia = Double.Parse(row["TriGia"].ToString());
            }
            catch { }
            try
            {
                sach.NgayNhap = (DateTime)row["NgayNhap"];
            }
            catch { }
            return sach;
        }

        //----------------------------------------------------------------------
        //Desc: lấy sách
        //----------------------------------------------------------------------
        public DTOSach LaySach(string masach)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spSach_LaySach";
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15) { Value = masach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaSach);
            if (data == null || data.Rows.Count == 0)
                return null;
            return ConvertToSach(data.Rows[0]);
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách thông tin sách
        //----------------------------------------------------------------------
        public DataTable LayDSThongTinSach()
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spSach_LayDanhSachThongTinSach";
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách thông tin sách mượn
        //----------------------------------------------------------------------
        public DataTable LayDSThongTinSachMuon()
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spSach_LayDanhSachThongTinSachMuon";
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
        }

        //----------------------------------------------------------------------
        //Desc: lấy mã sách
        //----------------------------------------------------------------------
        public string LayMaSach(string madausach, long namxb, string nhaxb)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spSach_LayMaSachTuDauSach";
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = madausach };
            SqlParameter sqlprNamXB = new SqlParameter("@NamXB", SqlDbType.Int) { Value = namxb };
            SqlParameter sqlprNhaXB = new SqlParameter("@NhaXB", SqlDbType.NVarChar, 70) { Value = nhaxb };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaDauSach, sqlprNamXB, sqlprNhaXB);
            if (data == null || data.Rows.Count == 0)
                return null;
            return data.Rows[0]["MaSach"].ToString();
        }

        //----------------------------------------------------------------------
        //Desc: đếm lần xuất bản
        //----------------------------------------------------------------------
        public int DemLanXuatBan(string madausach, long namxb, string nhaxb)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spSach_DemLanXuatBan";
            SqlParameter sqlprMaSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = madausach };
            SqlParameter sqlprNamXB = new SqlParameter("@NamXB", SqlDbType.Int) { Value = namxb };
            SqlParameter sqlprNhaXB = new SqlParameter("@NhaXB", SqlDbType.NVarChar, 70) { Value = nhaxb };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaSach, sqlprNamXB, sqlprNhaXB);
            if (obj == null)
                return -1;
            int dem;
            try
            {
                dem = (int)obj;
            }
            catch
            {
                return -1;
            }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm sách
        //----------------------------------------------------------------------
        public int DemSach(string madausach)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spSach_DemSach";
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = madausach };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaDauSach);

            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm mã sách
        //----------------------------------------------------------------------
        public int DemMaSach(string masach)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spSach_DemMaSach";
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15) { Value = masach };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaSach);

            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: lấy số lượng sách
        //----------------------------------------------------------------------
        public int LaySoLuongSach(string masach)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spSach_LaySoLuongSach";
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15) { Value = masach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaSach);
            if (data == null || data.Rows.Count == 0)
                return -1;
            int soluong;
            try
            {
                soluong = Int32.Parse(data.Rows[0]["SoLuong"].ToString());
            }
            catch { return -1; }
            return soluong;
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật sách
        //----------------------------------------------------------------------
        public bool CapNhat(DTOSach sach)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spSach_CapNhatSach";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac", SqlDbType.Int) { Value = 1 };
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15) { Value = sach.MaSach };
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = sach.MaDauSach };
            SqlParameter sqlprSoLuong = new SqlParameter("@SoLuong", SqlDbType.Int) { Value = sach.SoLuong };
            SqlParameter sqlprNamXB = new SqlParameter("@NamXB", SqlDbType.Int) { Value = sach.NamXB };
            SqlParameter sqlprNhaXB = new SqlParameter("@NhaXB", SqlDbType.NVarChar, 70) { Value = sach.NhaXB };
            SqlParameter sqlprTriGia = new SqlParameter("@TriGia", SqlDbType.Money) { Value = sach.TriGia };
            SqlParameter sqlprNgayNhap = new SqlParameter("@NgayNhap", SqlDbType.Date) { Value = sach.NgayNhap };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaSach,
                sqlprMaDauSach, sqlprSoLuong, sqlprNamXB, sqlprNhaXB, sqlprTriGia, sqlprNgayNhap);
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật số lượng
        //----------------------------------------------------------------------
        public bool CapNhatSoLuong(string masach, int soluongmoi)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spSach_CapNhatSoLuong";
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15) { Value = masach };
            SqlParameter sqlprSoLuong = new SqlParameter("@SoLuong", SqlDbType.Int){ Value = soluongmoi};
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprMaSach, sqlprSoLuong);
        }

        //----------------------------------------------------------------------
        //Desc: tra cứu sách
        //----------------------------------------------------------------------
        public DataTable TraCuuSach(string masach, string tendausach, string matl, string matg,
            long namxb, string nhaxb, object NgayNhap, double trigiamin, double trigiamax)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spSach_TraCuuSach";
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15){ Value = masach };
            SqlParameter sqlprTenDauSach = new SqlParameter("@TenDauSach", SqlDbType.NVarChar, 70)
            { Value = tendausach };
            SqlParameter sqlprMaTL = new SqlParameter("@MaTL", SqlDbType.VarChar, 15){ Value = matl };
            SqlParameter sqlprMaTG = new SqlParameter("@MaTG", SqlDbType.VarChar, 15){ Value = matg };
            SqlParameter sqlprNamXB = new SqlParameter("@NamXB", SqlDbType.Int);
            if (namxb < 0)
                sqlprNamXB.Value = null;
            else
                sqlprNamXB.Value = (int)namxb;
            SqlParameter sqlprNhaXB = new SqlParameter("@NhaXB", SqlDbType.NVarChar, 70){ Value = nhaxb };
            SqlParameter sqlprNgayNhap = new SqlParameter("@NgayNhap", SqlDbType.Date){ Value = NgayNhap };
            SqlParameter sqlprTriGiaMin = new SqlParameter("@TriGiaMin", SqlDbType.Float) { Value = trigiamin };
            SqlParameter sqlprTriGiaMax = new SqlParameter("@TriGiaMax", SqlDbType.Float);
            if (trigiamax > 0)
                sqlprTriGiaMax.Value = trigiamax;
            else
                sqlprTriGiaMax.Value = null;
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaSach, sqlprTenDauSach,
                sqlprMaTL, sqlprMaTG, sqlprNamXB, sqlprNhaXB, sqlprNgayNhap, sqlprTriGiaMin, sqlprTriGiaMax);
        }
    }
}
